﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace DAO
{
    public class DAOBanHang:DataProvider
    {
        public static DataTable DSMonAn()
        {
            SqlConnection MoKetNoi = KetNoi();
            string chuoilenh = "select mamonan,tenmonan,giatien from monan";
            DataTable DSMon = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(chuoilenh, MoKetNoi);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DSMon = ds.Tables[0];
            DongKetNoi();
            return DSMon;
        }
        public static DataTable KQTimKH(string chuoi)
        {
            SqlConnection MoKetNoi = KetNoi();
            string chuoilenh = "select * from khachhang where hoten like '%"+chuoi+"%'";
            DataTable ketqua = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(chuoilenh, MoKetNoi);
            DataSet ds = new DataSet();
            da.Fill(ds);
            ketqua = ds.Tables[0];
            DongKetNoi();
            return ketqua;
        }
        public static DataTable KQTimMonAn(string chuoi)
        {
            SqlConnection MoKetNoi = KetNoi();
            string chuoilenh = "select mamonan,tenmonan,giatien from monan where tenmonan like '%" + chuoi + "%'";
            DataTable ketqua = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(chuoilenh, MoKetNoi);
            DataSet ds = new DataSet();
            da.Fill(ds);
            ketqua = ds.Tables[0];
            DongKetNoi();
            return ketqua;
        }
        public static int LuuPhieuXuat(string makh, DateTime ngay, float tongtien)
        {
            SqlConnection MoKetNoi = KetNoi();
            string chuoilenh = "insert into phieuxuat(makh,ngayxuat,thanhtien) values(@ma,@date,@tong)";
            SqlCommand lenh = new SqlCommand(chuoilenh, MoKetNoi);
            SqlParameter values = new SqlParameter("@ma",makh);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@date", ngay);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@tong", tongtien);
            lenh.Parameters.Add(values);
            lenh.ExecuteNonQuery();
            string chuoilenh2 = "select @@IDENTITY";
            lenh = new SqlCommand(chuoilenh2, MoKetNoi);
            int result = int.Parse(lenh.ExecuteScalar().ToString());
            DongKetNoi();
            return result;
        }
        public static int LuuChiTietPhieuXuat(int maphieuxuat, DataTable phieu)
        {
            SqlConnection MoKetNoi = KetNoi();
            int result = 0;
            for (int i = 0; i < phieu.Rows.Count; i++)
            {
                string chuoilenh = "insert into chitietphieuxuat(maphieuxuat,mamonan,soluong,giatien,thanhtien) values(@mapx,@mamon,@sl,@gia,@thanhtien)";
                SqlCommand lenh = new SqlCommand(chuoilenh, MoKetNoi);
                SqlParameter values = new SqlParameter("@mapx", maphieuxuat);
                lenh.Parameters.Add(values);
                values = new SqlParameter("@mamon", phieu.Rows[i][3].ToString().Trim());
                lenh.Parameters.Add(values);
                values = new SqlParameter("@sl", int.Parse(phieu.Rows[i][5].ToString().Trim()));
                lenh.Parameters.Add(values);
                values = new SqlParameter("@gia", float.Parse(phieu.Rows[i][4].ToString().Trim()));
                lenh.Parameters.Add(values);
                values = new SqlParameter("@thanhtien", float.Parse(phieu.Rows[i][6].ToString().Trim()));
                lenh.Parameters.Add(values);
                result = lenh.ExecuteNonQuery();
            }
            DongKetNoi();
            return result;
        }
        public static DataTable BangKhuyenMai()
        {
            SqlConnection MoKetNoi = KetNoi();
            string chuoilenh = "select tonggiatri,diemcong,diemgioihan,sotienduocgiam from khuyenmai";
            DataTable ketqua;
            SqlDataAdapter da = new SqlDataAdapter(chuoilenh, MoKetNoi);
            DataSet ds = new DataSet();
            da.Fill(ds);
            ketqua = ds.Tables[0];
            DongKetNoi();
            return ketqua;
        }
        public static int CapNhatDiemKH(int id, int diem)
        {
            SqlConnection MoKetNoi = KetNoi();
            string chuoilenh = "update khachhang set diemtichluy=@diem where makh=@id";
            SqlCommand lenh = new SqlCommand(chuoilenh, MoKetNoi);
            SqlParameter values = new SqlParameter("@diem", diem);
            lenh.Parameters.Add(values);
            values = new SqlParameter("@id", id);
            lenh.Parameters.Add(values);
            int result = lenh.ExecuteNonQuery();
            DongKetNoi();
            return result;
        }
    }
}
